USE master
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_KillDbProcesses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_KillDbProcesses]
GO

PRINT 'Creating sp_AddWindowsUserLogin...'
GO

CREATE PROCEDURE dbo.sp_KillDbProcesses
(
	@database_name sysname
)
AS
SET NOCOUNT ON
CREATE TABLE #process_info
	(
	process_info_id int IDENTITY(1,1),
	spid smallint,
	ecid smallint,
	status nchar(30),
	loginame nchar(128),
	hostname nchar(128),
	blk char(5),
	dbname nchar(128),
	cmd nchar(16),
	request_id int
	)

INSERT INTO #process_info
EXEC master..sp_who
DECLARE
	@process_count int,
	@counter int,
	@spid smallint,
	@dbname nchar(128),
	@sql varchar(100)
SET @counter = 1
SET @process_count = (SELECT COUNT(*) FROM #process_info)
WHILE @counter <= @process_count
BEGIN
	SELECT @spid = spid, @dbname = dbname FROM #process_info WHERE process_info_id = @counter
	IF @dbname = @database_name
	BEGIN
		SET @sql = 'KILL ' + CAST(@spid AS varchar(3))
		PRINT 'Killing the following spid: ' + CAST(@spid AS varchar(3))
		EXECUTE(@sql)
	END
	SET @counter = @counter + 1
END
DROP TABLE #process_info
GO